﻿using EcoSystem.Office.Enums;
using Newtonsoft.Json;
using NPOI.HSSF.UserModel;
using NPOI.SS.UserModel;
using NPOI.SS.Util;
using NPOI.XSSF.UserModel;
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.IO;
using System.Linq;
using System.Reflection;


namespace EcoSystem.Office.Excel
{
    /// <summary>
    /// 
    /// </summary>
    public static class ExcelExportService
    {



        /// <summary>
        /// 导出模版
        /// </summary>
        /// <param name="options"></param>
        /// <param name="type"></param>
        /// <returns></returns>
        public static MemoryStream WriteTemplate<T>(ExcelExportOptions<T> options, ExcelFileType type = ExcelFileType.Excel97_2003) where T : class
        {
            var columns = options.Columns;
            int rowIndex = 0;
            var columnCount = columns.Count();

            IWorkbook book;
            if (type == ExcelFileType.Excel2007)
            {
                book = new XSSFWorkbook();
            }
            else
            {
                book = new HSSFWorkbook();
            }
            ISheet sheet = book.CreateSheet(string.IsNullOrEmpty(options.SheetName) ? "导出" : options.SheetName);

            IRow row = sheet.CreateRow(rowIndex);
            ICellStyle headerStyle = book.CreateCellStyle();
            headerStyle.Alignment = HorizontalAlignment.Center;
            headerStyle.VerticalAlignment = VerticalAlignment.Center;
            row.HeightInPoints = 20;

            //创建表头列
            for (var colIndex = 0; colIndex < columnCount; colIndex++)
            {
                var col = columns[colIndex];
                var cell = row.CreateCell(colIndex);
                cell.SetCellValue(col.Caption);
                cell.CellStyle = headerStyle;

            }


            var validates = new List<ExcelCellValidateData>();

            //创建数据验证区域
            for (var colIndex = 0; colIndex < columnCount; colIndex++)
            {
                var col = columns[colIndex];
                var validateOptions = new string[] { };
                if (col.Validate != null && col.Validate.Options != null && col.Validate.Options.Count > 0)
                {
                    validates.Add(new ExcelCellValidateData()
                    {
                        Caption = col.Caption,
                        FieldName = col.FieldName,
                        Options = col.Validate.Options
                    });
                    validateOptions = col.Validate.Options.Select(s => s.CellText).ToArray();

                }
                else if (col.DataType.IsEnum)
                {
                    //处理枚举
                    var enumOptions = EnumToCellDropdownList(col.DataType);

                    validates.Add(new ExcelCellValidateData()
                    {
                        Caption = col.Caption,
                        FieldName = col.FieldName,
                        Options = enumOptions
                    });
                    validateOptions = enumOptions.Select(s => s.CellText).ToArray();
                }

                if (validateOptions.Length > 0)
                {
                    XSSFDataValidationHelper helper = new XSSFDataValidationHelper((XSSFSheet)sheet);
                    CellRangeAddressList regions = new CellRangeAddressList(1, 65535, colIndex, colIndex);
                    IDataValidation validation = helper.CreateValidation(helper.CreateExplicitListConstraint(validateOptions), regions);
                    validation.CreateErrorBox("错误", "输入值无效，请下拉选择正确的值!");//不符合约束时的提示  
                    validation.ShowErrorBox = true;
                    sheet.AddValidationData(validation);
                }
            }

            ISheet dataSheet = book.CreateSheet("DATA");
            book.SetSheetHidden(1, SheetState.Hidden);

            var dataRow = dataSheet.CreateRow(1);
            var dataCell = dataRow.CreateCell(0);
            dataCell.SetCellValue(JsonConvert.SerializeObject(validates));

            // 写入到Stream
            System.IO.MemoryStream ms = new System.IO.MemoryStream();
            book.Write(ms);
            book = null;
            return ms;
        }

        /// <summary>
        /// 
        /// </summary>
        /// <typeparam name="TModel"></typeparam>
        /// <param name="list"></param>
        /// <param name="options"></param>
        /// <param name="type"></param>
        /// <returns></returns>
        public static MemoryStream WriteExcelToStream<TModel>(List<TModel> list,
            ExcelExportOptions<TModel> options,
            ExcelFileType type = ExcelFileType.Excel97_2003) where TModel : class
        {
            //List<ExportColumn> columns, string filePath, string caption, string sheetName
            if (null != list)
            {
                var columns = options.Columns;
                int rowIndex = 0;
                var columnCount = columns.Count();

                IWorkbook book;
                if (type == ExcelFileType.Excel2007)
                {
                    book = new XSSFWorkbook();
                }
                else
                {
                    book = new HSSFWorkbook();
                }
                ISheet sheet = book.CreateSheet(string.IsNullOrEmpty(options.SheetName) ? "导出" : options.SheetName);
                IRow row = sheet.CreateRow(rowIndex);

                var titleStyle = CreateHeaderStyle(book, options);
                if (!string.IsNullOrEmpty(options.Caption))
                {
                    //创建表头标题
                    row.HeightInPoints = options.RowHeight == 0 ? 20 : options.RowHeight;

                    CellRangeAddress region = null;
                    if (columnCount > 1)
                    {
                        region = new CellRangeAddress(0, 0, 0, columnCount - 1);
                        sheet.AddMergedRegion(region);
                    }
                    var cell = row.CreateCell(0);
                    cell.SetCellValue(options.Caption);
                    cell.CellStyle = titleStyle;
                    if (columnCount > 1 && region != null)
                    {
                        RegionUtil.SetBorderLeft(1, region, sheet);//左
                        RegionUtil.SetBorderRight(1, region, sheet);//右
                        RegionUtil.SetBorderTop(1, region, sheet);//顶
                        RegionUtil.SetBorderBottom(1, region, sheet);//底
                    }
                    rowIndex++;
                    row = sheet.CreateRow(rowIndex);
                }

                //自定义表单行数据
                if (options.HeaderRows?.Count > 0)
                {
                    //保存对应行的列偏移值
                    var cellOffset = new Dictionary<int, List<KeyValuePair<int, int>>>();

                    var customHeaderStyle = CreateCustomHeaderStyle(book, options);
                    var customHeaderCenterStyle = CreateCustomHeaderStyle(book, options);
                    customHeaderCenterStyle.Alignment = HorizontalAlignment.Center;
                    var customHeaderRightStyle = CreateCustomHeaderStyle(book, options);
                    customHeaderRightStyle.Alignment = HorizontalAlignment.Right;

                    foreach (var item in options.HeaderRows)
                    {
                        var cellIndex = 0;
                        row.HeightInPoints = options.RowHeight == 0 ? 20 : options.RowHeight;
                        foreach (var cellItem in item.Cells)
                        {
                            var cellSpan = cellItem.Span > 0 ? cellItem.Span : 1;
                            var rowSpan = cellItem.RowSpan > 0 ? cellItem.RowSpan : 1;
                            if (rowSpan > 1)
                            {
                                //计算后续行对应列偏移值
                                for (var i = rowIndex + 1; i < rowIndex + rowSpan; i++)
                                {
                                    var offset = new KeyValuePair<int, int>(cellIndex, cellSpan);
                                    if (cellOffset.ContainsKey(i))
                                    {
                                        cellOffset[i].Add(offset);
                                    }
                                    else
                                    {
                                        var offsets = new List<KeyValuePair<int, int>>();
                                        offsets.Add(offset);
                                        cellOffset.Add(i, offsets);
                                    }
                                }
                            }

                            //获取当前行列偏移值
                            var rowOffsets = cellOffset.GetValueOrDefault(rowIndex, null);
                            while (rowOffsets != null && rowOffsets.Any(x => x.Key == cellIndex))
                            {
                                var offsetSpan = rowOffsets.FirstOrDefault(x => x.Key == cellIndex).Value;
                                cellIndex += offsetSpan;
                            }

                            CellRangeAddress region = null;
                            if (rowSpan > 1 || cellSpan > 1)
                            {
                                region = new CellRangeAddress(rowIndex, rowIndex + rowSpan - 1, cellIndex, cellIndex + cellSpan - 1);
                                sheet.AddMergedRegion(region);
                            }
                            var cell = row.CreateCell(cellIndex);
                            SetCellValue(cell, cellItem.Value);
                            switch (cellItem.Align)
                            {
                                case HorizontalAlignment.Center:
                                    cell.CellStyle = customHeaderCenterStyle;
                                    break;
                                case HorizontalAlignment.Right:
                                    cell.CellStyle = customHeaderRightStyle;
                                    break;
                                default:
                                    cell.CellStyle = customHeaderStyle;
                                    break;
                            }
                            if (region != null)
                            {
                                RegionUtil.SetBorderLeft(1, region, sheet);//左
                                RegionUtil.SetBorderRight(1, region, sheet);//右
                                RegionUtil.SetBorderTop(1, region, sheet);//顶
                                RegionUtil.SetBorderBottom(1, region, sheet);//底
                            }
                            cellIndex += cellItem.Span;
                        }
                        rowIndex++;
                        row = sheet.CreateRow(rowIndex);
                    }
                }
                var headerStyle = CreateHeaderStyle(book, options);

                //创建表头列
                if (options.HideColumnTitleRow != true)
                {
                    int colIndex = 0;
                    foreach (var col in columns)
                    {
                        var cell = row.CreateCell(colIndex);
                        cell.SetCellValue(col.Caption);
                        cell.CellStyle = headerStyle;
                        colIndex++;
                    }
                }
                else
                {
                    rowIndex--;
                }

                ICellStyle style = CreateBodyStyle(book);
                ICellStyle dateStyle = CreateDateTimeStyle(book, options.DateTimeFormatter);


                //创建内容行
                foreach (object item in list)
                {
                    rowIndex++;
                    IRow row2 = sheet.CreateRow(rowIndex);
                    row2.HeightInPoints = options.RowHeight == 0 ? 20 : options.RowHeight;
                    int colIndex = 0;
                    foreach (var col in columns)
                    {
                        var value = GetPropertyValue(item, col.FieldName);
                        var cell = row2.CreateCell(colIndex);
                        cell.CellStyle = style;
                        if (value is string)
                        {
                            cell.SetCellValue(Convert.ToString(value));
                        }
                        else if (value is DateTime)
                        {
                            cell.SetCellValue(Convert.ToDateTime(value));

                            cell.CellStyle = dateStyle;
                        }
                        else if (value is bool)
                        {
                            cell.SetCellValue(Convert.ToBoolean(value) ? "是" : "否");
                        }
                        else if (value is byte || value is short || value is int ||
                            value is long || value is decimal || value is float || value is double)
                        {
                            cell.SetCellValue(Convert.ToDouble(value));
                        }
                        else if (value is IRichTextString)
                        {
                            cell.SetCellValue((IRichTextString)value);
                        }
                        else if (value is Enum)
                        {
                            cell.SetCellValue(GetDescription((Enum)value));
                        }
                        else
                        {
                            cell.SetCellValue("");
                        }
                        colIndex++;
                    }
                }

                if (!string.IsNullOrWhiteSpace(options.FooterContent))
                {
                    rowIndex++;
                    ICellStyle footerStyle = CreateFooterStyle(book);
                    var row3 = sheet.CreateRow(rowIndex);
                    var lineCount = options.FooterContent.Where(x => x == '\n').Count() + 1;
                    row3.HeightInPoints = 20 * lineCount + 10;
                    CellRangeAddress region = null;
                    if (columnCount > 1)
                    {
                        region = new CellRangeAddress(rowIndex, rowIndex, 0, columnCount - 1);
                        sheet.AddMergedRegion(region);
                    }
                    var cell = row3.CreateCell(0);
                    var content = string.Join("\r\n", options.FooterContent.Split('\n'));
                    cell.SetCellValue(content);
                    cell.CellStyle = footerStyle;
                    if (columnCount > 1 && region != null)
                    {
                        RegionUtil.SetBorderLeft(1, region, sheet);//左
                        RegionUtil.SetBorderRight(1, region, sheet);//右
                        RegionUtil.SetBorderTop(1, region, sheet);//顶
                        RegionUtil.SetBorderBottom(1, region, sheet);//底
                    }
                }

                //设置列宽
                for (int i = 0; i < columnCount; i++)
                {
                    if (options.AutoSizeColumn)
                    {
                        sheet.AutoSizeColumn(i);
                    }
                    //设置列宽为20字符宽度
                    else
                    {
                        var width = columns[i].Width;
                        if (width <= 0)
                        {
                            sheet.SetColumnWidth(i, 20 * 256);
                        }
                        else
                        {
                            sheet.SetColumnWidth(i, width * 256);
                        }
                    }
                }

                // 写入到Stream
                System.IO.MemoryStream ms = new System.IO.MemoryStream();
                book.Write(ms);
                book = null;
                return ms;
            }
            else
            {
                throw new Exception("要导出的List<TModel>参数不允许为空", new ArgumentNullException());
            }
        }

        /// <summary>
        /// 设置单元格式值
        /// </summary>
        /// <param name="cell"></param>
        /// <param name="value"></param>
        private static void SetCellValue(ICell cell, object value)
        {
            if (value is Double)
            {
                cell.SetCellValue((double)value);
            }
            else if (value is float || value is decimal)
            {
                cell.SetCellValue((float)value);
            }
            else if (value is int)
            {
                cell.SetCellValue((int)value);
            }
            else if (value is long)
            {
                cell.SetCellValue((long)value);
            }
            else if (value is short)
            {
                cell.SetCellValue((short)value);
            }
            else if (value is byte)
            {
                cell.SetCellValue((byte)value);
            }
            else if (value is bool)
            {
                cell.SetCellValue((bool)value);
            }
            else if (value is DateTime)
            {
                cell.SetCellValue((DateTime)value);
            }
            else if (value is IRichTextString)
            {
                cell.SetCellValue((IRichTextString)value);
            }
            else if (value is string)
            {
                cell.SetCellValue((string)value);
            }
        }


        /// <summary>
        /// 
        /// </summary>
        /// <typeparam name="TModel"></typeparam>
        /// <param name="list"></param>
        /// <param name="options"></param>
        /// <param name="filePath"></param>
        /// <returns></returns>
        /// <exception cref="Exception"></exception>
        public static bool WriteExcelToFile<TModel>(List<TModel> list,
            ExcelExportOptions<TModel> options,
            string filePath) where TModel : class
        {
            if (!string.IsNullOrEmpty(filePath))
            {
                string ext = Path.GetExtension(filePath);
                ExcelFileType type =
                    ext.Equals(".xlsx", StringComparison.OrdinalIgnoreCase) ?
                    ExcelFileType.Excel2007 : ExcelFileType.Excel97_2003;
                var ms = WriteExcelToStream<TModel>(list, options, type);
                using (FileStream fs = new FileStream(filePath, FileMode.Create, FileAccess.Write))
                {
                    try
                    {
                        byte[] data = ms.ToArray();
                        fs.Write(data, 0, data.Length);
                        fs.Flush();
                        return true;
                    }
                    catch (Exception ex)
                    {
                        throw ex;
                    }
                }
            }
            else
            {
                throw new Exception("输出文件参数格式错误，文件路径不允许为空");
            }
        }


        #region 私有方法

        /// <summary>
        /// 获取一个对象某一字段值
        /// </summary>
        /// <param name="model">对象实体</param>
        /// <param name="propertyName">属性名</param>
        /// <returns></returns>
        private static object GetPropertyValue(object model, string propertyName)
        {
            if (propertyName.IndexOf(".") > 0)
            {
                object data = model;
                string[] arrs = propertyName.Split('.');
                foreach (var s in arrs)
                {
                    data = GetPropertyValue<object>(data, s);
                    if (data == null)
                    {
                        return null;
                    }
                }
                return data;
            }
            else
            {
                return GetPropertyValue<object>(model, propertyName);
            }
        }

        /// <summary>
        /// 扩展动态获取对象指定属性值
        /// </summary>
        /// <typeparam name="TResult">返回值类型</typeparam>
        /// <param name="model">要获取属性的对象</param>
        /// <param name="propertyName">属性名称</param>
        /// <exception cref="HongzeNullException">当调用的实体为空、方法名为空或不存在时抛出</exception>
        /// <returns>返回属性值</returns>
        private static TResult GetPropertyValue<TResult>(object model, string propertyName)
        {
            if (model == null)
            {
                throw new ArgumentNullException("动态获取属性值 - 对象实体不允许为空");
            }

            if (model is IDictionary<string, object>)
            {
                var dict = model as IDictionary<string, object>;
                object value;
                dict.TryGetValue(propertyName, out value);
                return (TResult)value;
            }
            if (string.IsNullOrEmpty(propertyName))
            {
                throw new ArgumentNullException("动态获取属性值 - 属性名不允许为空");
            }
            var type = model.GetType();

            var pInfo = type.GetProperty(propertyName);
            if (pInfo == null)
            {
                throw new ArgumentNullException($"动态获取属性值 - 不存在此属性值[{propertyName}]");
            }
            return (TResult)pInfo.GetValue(model);
        }

        /// <summary>
        /// 获取枚举对象上的特性
        /// <para>
        /// <example>
        /// Sex.Male.GetAttribute(-DescriptionAttribute-)().Description
        /// </example>
        /// </para>
        /// </summary>
        /// <typeparam name="T">“特性“对象，必须是attribute</typeparam>
        /// <param name="value">枚举类型</param>
        /// <returns>枚举的特性</returns>
        /// <summary>
        /// 获取自定义attribute 
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="enumObj"></param>
        /// <returns></returns>
        private static T GetEnumAttribute<T>(Enum enumObj) where T : Attribute
        {
            Type type = enumObj.GetType();
            Attribute attr = null;
            String enumName = Enum.GetName(type, enumObj);
            if (enumName == null) { return null; }
            FieldInfo field = type.GetField(enumName);
            attr = field.GetCustomAttribute(typeof(T), false);
            return (T)attr;
        }

        /// <summary>
        /// 获取枚举文字内容
        /// </summary>
        /// <param name="enumObj"></param>
        /// <returns></returns>
        private static string GetDescription(Enum enumObj)
        {
            var desc = GetEnumAttribute<DescriptionAttribute>(enumObj);
            if (desc == null)
            {
                Type type = enumObj.GetType();
                return Enum.GetName(type, enumObj);
            }
            else
            {
                return desc.Description;
            }
        }

        /// <summary>
        /// 
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="workbook"></param>
        /// <param name="options"></param>
        /// <returns></returns>
        private static ICellStyle CreateHeaderStyle<T>(IWorkbook workbook, ExcelExportOptions<T> options) where T : class
        {
            ICellStyle style = workbook.CreateCellStyle();
            //设置单元格的样式：水平对齐居中
            style.Alignment = HorizontalAlignment.Center;
            style.VerticalAlignment = VerticalAlignment.Center;

            //新建一个字体样式对象
            IFont font = workbook.CreateFont();
            //设置字体加粗样式
            font.IsBold = true;

            //使用SetFont方法将字体样式添加到单元格样式中 
            style.SetFont(font);

            style.BorderTop = BorderStyle.Thin;
            style.BorderRight = BorderStyle.Thin;
            style.BorderBottom = BorderStyle.Thin;
            style.BorderLeft = BorderStyle.Thin;

            return style;
        }

        /// <summary>
        /// 
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="workbook"></param>
        /// <param name="options"></param>
        /// <returns></returns>
        private static ICellStyle CreateCustomHeaderStyle<T>(IWorkbook workbook, ExcelExportOptions<T> options) where T : class
        {
            ICellStyle style = workbook.CreateCellStyle();
            //设置单元格的样式：水平对齐居中
            style.VerticalAlignment = VerticalAlignment.Center;

            style.BorderTop = BorderStyle.Thin;
            style.BorderRight = BorderStyle.Thin;
            style.BorderBottom = BorderStyle.Thin;
            style.BorderLeft = BorderStyle.Thin;

            return style;
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="workbook"></param>
        /// <returns></returns>
        private static ICellStyle CreateBodyStyle(IWorkbook workbook)
        {
            ICellStyle style = workbook.CreateCellStyle();
            //设置单元格的样式：水平对齐居中
            style.Alignment = HorizontalAlignment.Center;
            style.VerticalAlignment = VerticalAlignment.Center;
            //新建一个字体样式对象
            IFont font = workbook.CreateFont();
            //设置字体加粗样式
            font.IsBold = false;

            //使用SetFont方法将字体样式添加到单元格样式中 
            style.SetFont(font);

            style.BorderTop = BorderStyle.Thin;
            style.BorderRight = BorderStyle.Thin;
            style.BorderBottom = BorderStyle.Thin;
            style.BorderLeft = BorderStyle.Thin;

            return style;
        }

        private static ICellStyle CreateFooterStyle(IWorkbook workbook)
        {
            ICellStyle style = CreateBodyStyle(workbook);
            style.Alignment = HorizontalAlignment.Left;
            style.WrapText = true;
            return style;
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="workbook"></param>
        /// <param name="formater"></param>
        /// <returns></returns>
        private static ICellStyle CreateDateTimeStyle(IWorkbook workbook, string formater)
        {
            ICellStyle style = CreateBodyStyle(workbook);
            IDataFormat dataformat = workbook.CreateDataFormat();
            if (string.IsNullOrWhiteSpace(formater))
            {
                style.DataFormat = dataformat.GetFormat("yyyy年MM月dd日 HH:mm:ss");

            }
            else
            {
                style.DataFormat = dataformat.GetFormat(formater.Trim());
            }
            return style;
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="type"></param>
        /// <returns></returns>
        private static List<ExcelCellDropdownItem> EnumToCellDropdownList(Type type)
        {
            var emValues = EnumHelper.GetEnums(type);
            return emValues.Select(
                s => new ExcelCellDropdownItem()
                {
                    CellText = s.Descrption,
                    CellValue = s.Value.ToString()
                }).ToList();
        }


        #endregion 私有方法


    }
}
